import random

from flask import Blueprint, request, jsonify
import pymysql
from math import ceil
import logging

from sqlalchemy import func

from python import db
from python.model.model import Attraction

menu2_bp = Blueprint('menu2', __name__, url_prefix='/menu')

def get_conn():
    return pymysql.connect(
        host="localhost",
        user="root",
        password='root',
        database="stat",
        charset='utf8mb4',  # 统一使用 utf8mb4
        cursorclass=pymysql.cursors.DictCursor
    )


@menu2_bp.route('/selectAllProvince',methods=['POST'])
def selectallProvince():
    try:
        with get_conn() as conn:
            with conn.cursor() as cursor:
                sql = "SELECT DISTINCT province FROM attractions ORDER BY province"
                cursor.execute(sql)
                provinces = [row['province'] for row in cursor.fetchall()]
                return jsonify({"code": 0, "data": provinces})
    except Exception as e:
        return jsonify({"code": 1, "msg": str(e)}), 500


@menu2_bp.route('/selectAllCityByProvince', methods=['POST'])
def selectallCityByProvince():
    try:
        province = request.form.get("province")
        if not province:
            return jsonify({"code": 1, "msg": "省份不能为空"}), 400

        with get_conn() as conn:
            with conn.cursor() as cursor:
                sql = "SELECT DISTINCT city FROM attractions WHERE province = %s ORDER BY city"
                cursor.execute(sql, (province,))
                cities = [row['city'] for row in cursor.fetchall()]
                return jsonify({"code": 0, "data": cities})
    except Exception as e:
        return jsonify({"code": 1, "msg": str(e)}), 500




# 配置日志
logging.basicConfig(level=logging.DEBUG)
logger = logging.getLogger(__name__)

# 修改后的menu2_bp后端代码（保留menu2版本）
@menu2_bp.route('/get_region_stats', methods=['GET'])  # 改为GET方法
def get_region_stats():
    try:
        # 获取参数（改为从args获取）
        page = request.args.get("page", 1, type=int)
        limit = request.args.get("limit", 10, type=int)
        province = request.args.get("province", "").strip()
        city = request.args.get("city", "").strip()

        # 参数校验
        if page < 1: page = 1
        if limit < 1: limit = 10
        if limit > 100: limit = 100

        # 计算偏移量
        offset = (page - 1) * limit

        with get_conn() as conn:
            with conn.cursor() as cursor:
                # 基础查询SQL（保持不变）
                base_sql = """
                SELECT 
                    province,
                    city,
                    COUNT(*) AS attraction_count,
                    SUM(sales) AS total_sales,
                    SUM(CASE WHEN star_level IN ('4A', '5A') THEN 1 ELSE 0 END) AS high_grade_count,
                    AVG(price) AS avg_price
                FROM attractions
                """

                # 条件过滤（保持不变）
                conditions = []
                params = []
                if province:
                    conditions.append("province = %s")
                    params.append(province)
                if city:
                    conditions.append("city = %s")
                    params.append(city)

                where_sql = " WHERE " + " AND ".join(conditions) if conditions else ""

                # 分页查询（添加别名保证layui兼容）
                query_sql = base_sql + where_sql + """
                GROUP BY province, city
                ORDER BY province, city
                LIMIT %s OFFSET %s
                """
                cursor.execute(query_sql, params + [limit, offset])
                results = cursor.fetchall()

                # 总数查询（保持不变）
                count_sql = """
                SELECT COUNT(DISTINCT CONCAT(province, city)) AS total
                FROM attractions
                """ + where_sql
                cursor.execute(count_sql, params)
                total = cursor.fetchone()['total']

                # 统计信息查询（保持不变）
                stats_sql = """
                SELECT 
                    SUM(sales) AS grand_total_sales,
                    SUM(CASE WHEN star_level IN ('4A', '5A') THEN 1 ELSE 0 END) AS grand_high_grade,
                    AVG(price) AS grand_avg_price
                FROM attractions
                """ + where_sql
                cursor.execute(stats_sql, params)
                stats = cursor.fetchone()

                # 构造返回数据（添加layui需要的字段）
                data = []
                for i, row in enumerate(results, 1):
                    data.append({
                        "id": offset + i,
                        "province": row['province'],
                        "city": row['city'],
                        "attraction_count": row['attraction_count'] or 0,
                        "total_sales": row['total_sales'] or 0,
                        "high_grade_count": row['high_grade_count'] or 0,
                        "avg_price": round(float(row['avg_price'] or 0), 2),
                        "LAY_TABLE_INDEX": (page-1)*limit + i  # layui分页需要的索引
                    })

                return jsonify({
                    "code": 0,
                    "msg": "success",
                    "data": data,
                    "count": total,
                    "stats": {
                        "grand_total_sales": stats['grand_total_sales'] or 0,
                        "grand_high_grade": stats['grand_high_grade'] or 0,
                        "grand_avg_price": round(float(stats['grand_avg_price'] or 0), 2)
                    }
                })
    except Exception as e:
        logger.error(f"Error in get_region_stats: {str(e)}", exc_info=True)
        return jsonify({"code": 1, "msg": str(e)}), 500

    #新增
@menu2_bp.route('/get_ticket_price_distribution', methods=['GET'])
def get_ticket_price_distribution():
    try:
        with get_conn() as conn:
            with conn.cursor() as cursor:
                # 查询各价格区间的景点数量
                sql = """
                SELECT 
                    CASE 
                        WHEN price BETWEEN 0 AND 50 THEN '0-50'
                        WHEN price BETWEEN 50 AND 100 THEN '50-100'
                        WHEN price BETWEEN 100 AND 150 THEN '100-150'
                        WHEN price BETWEEN 150 AND 200 THEN '150-200'
                        WHEN price BETWEEN 200 AND 300 THEN '200-300'
                        WHEN price BETWEEN 300 AND 500 THEN '300-500'
                        WHEN price BETWEEN 500 AND 1000 THEN '500-1000'
                        ELSE '1000以上'
                    END AS price_range,
                    COUNT(*) AS count
                FROM attractions
                GROUP BY price_range
                ORDER BY 
                    CASE price_range
                        WHEN '0-50' THEN 1
                        WHEN '50-100' THEN 2
                        WHEN '100-150' THEN 3
                        WHEN '150-200' THEN 4
                        WHEN '200-300' THEN 5
                        WHEN '300-500' THEN 6
                        WHEN '500-1000' THEN 7
                        ELSE 8
                    END
                """
                cursor.execute(sql)
                results = cursor.fetchall()
                if not results:  # 检查结果是否为空
                    return jsonify({"code": 1, "msg": "没有查询到数据"}), 404
                return jsonify({"code": 0, "data": results})
    except Exception as e:
        return jsonify({"code": 1, "msg": str(e)}), 500


@menu2_bp.route('/get_rose_data', methods=['GET'])
def get_rose_data():
    try:
        with get_conn() as conn:
            with conn.cursor() as cursor:
                # 查询各省4A-5A景区数量
                sql = """
                SELECT 
                    province AS name,
                    COUNT(*) AS value
                FROM attractions
                WHERE star_level IN ('4A', '5A')
                GROUP BY province
                ORDER BY value DESC
                """
                cursor.execute(sql)
                results = cursor.fetchall()

                return jsonify({
                    "code": 0,
                    "msg": "success",
                    "data": results
                })
    except Exception as e:
        return jsonify({
            "code": 1,
            "msg": str(e),
            "data": []
        }), 500


@menu2_bp.route('/get_scatter_star_data', methods=['GET'])
def get_scatter_star_data():
    try:
        with get_conn() as conn:
            with conn.cursor() as cursor:
                # 查询各省4A-5A景区数量
                sql = """
                SELECT 
                    province AS name,
                    COUNT(*) AS value
                FROM attractions
                WHERE star_level IN ('4A', '5A')
                GROUP BY province
                ORDER BY value DESC
                """
                cursor.execute(sql)
                results = cursor.fetchall()

                return jsonify({
                    "code": 0,
                    "msg": "success",
                    "data": results
                })
    except Exception as e:
        return jsonify({
            "code": 1,
            "msg": str(e),
            "data": []
        }), 500
# 获取价格区间数据
@menu2_bp.route('/get_price_distribution', methods=['GET'])
def get_price_distribution():
    try:
        with get_conn() as conn:
            with conn.cursor() as cursor:
                # 查询各价格区间的景点数量
                sql = """
                SELECT 
                    SUM(CASE WHEN price BETWEEN 0 AND 50 THEN 1 ELSE 0 END) AS '0-50',
                    SUM(CASE WHEN price BETWEEN 50 AND 100 THEN 1 ELSE 0 END) AS '50-100',
                    SUM(CASE WHEN price BETWEEN 100 AND 150 THEN 1 ELSE 0 END) AS '100-150',
                    SUM(CASE WHEN price BETWEEN 150 AND 200 THEN 1 ELSE 0 END) AS '150-200',
                    SUM(CASE WHEN price BETWEEN 200 AND 300 THEN 1 ELSE 0 END) AS '200-300',
                    SUM(CASE WHEN price BETWEEN 300 AND 500 THEN 1 ELSE 0 END) AS '300-500',
                    SUM(CASE WHEN price BETWEEN 500 AND 1000 THEN 1 ELSE 0 END) AS '500-1000',
                    SUM(CASE WHEN price > 1000 THEN 1 ELSE 0 END) AS '1000以上',
                    COUNT(*) AS total
                FROM attractions
                """
                cursor.execute(sql)
                result = cursor.fetchone()

                ranges = ['0-50', '50-100', '100-150', '150-200', '200-300', '300-500', '500-1000', '1000以上']
                counts = [
                    result['0-50'], result['50-100'], result['100-150'],
                    result['150-200'], result['200-300'], result['300-500'],
                    result['500-1000'], result['1000以上']
                ]

                return jsonify({
                    "code": 0,
                    "msg": "success",
                    "data": {
                        "ranges": ranges,
                        "counts": counts,
                        "total": result['total']
                    }
                })
    except Exception as e:
        return jsonify({
            "code": 1,
            "msg": str(e),
            "data": {
                "ranges": [],
                "counts": [],
                "total": 0
            }
        }), 500


import os
import jieba
from collections import Counter
import re


@menu2_bp.route('/get_word_cloud_data', methods=['GET'])
def get_word_cloud_data():
    try:
        # 加载停用词列表
        stopwords = set()
        # 获取当前文件所在目录
        current_dir = os.path.dirname(os.path.abspath(__file__))

        # 构建停用词文件路径
        stopwords_file = os.path.join(
            os.path.dirname(current_dir),  # 退到上级目录
            'data',
            'stopwords.txt'
        )

        logging.info(f"停用词文件路径: {stopwords_file}")

        if os.path.exists(stopwords_file):
            with open(stopwords_file, 'r', encoding='utf-8') as f:
                for line in f:
                    stopwords.add(line.strip())
        else:
            error_msg = f"停用词文件不存在: {stopwords_file}"
            logging.error(error_msg)
            return jsonify({
                "code": 1,
                "msg": error_msg,
                "data": []
            }), 500

        with get_conn() as conn:
            with conn.cursor() as cursor:
                # 获取所有景点简介文本
                sql = "SELECT introduction FROM attractions"
                cursor.execute(sql)
                all_introductions = [row['introduction'] for row in cursor.fetchall() if row['introduction']]

                # 合并所有简介文本
                all_text = ' '.join(all_introductions)

                # 使用jieba进行分词
                words = jieba.cut(all_text)

                # 过滤停用词和非中文词
                filtered_words = []
                for word in words:
                    word = word.strip()
                    # 过滤停用词、单字和非中文
                    if (len(word) > 1 and
                            word not in stopwords and
                            re.match(r'^[\u4e00-\u9fa5]+$', word)):
                        filtered_words.append(word)

                # 统计词频
                word_freq = Counter(filtered_words)
                # 获取前100个高频词
                top_words = word_freq.most_common(100)

                # 转换为词云需要的数据格式
                results = []
                for word, freq in top_words:
                    # 为每个关键词生成随机颜色
                    r = random.randint(0, 255)
                    g = random.randint(0, 255)
                    b = random.randint(0, 255)
                    results.append({
                        'name': word,
                        'value': freq,
                        'textStyle': {'color': f'rgb({r},{g},{b})'}
                    })

                return jsonify({
                    "code": 0,
                    "msg": "success",
                    "data": results
                })
    except Exception as e:
        logging.error(f"获取词云数据错误: {str(e)}", exc_info=True)
        return jsonify({
            "code": 1,
            "msg": str(e),
            "data": []
        }), 500


@menu2_bp.route('/high_grade_attractions')
def high_grade_attractions():
    """获取各省4A-5A景区数量"""
    logger.info("数据库调用: 查询各省4A-5A景区数量")
    try:
        results = db.session.query(
            Attraction.province,
            func.count(Attraction.id).label('count')
        ).filter(Attraction.star_level.in_(['4A', '5A'])) \
            .group_by(Attraction.province) \
            .order_by(func.count(Attraction.id).desc()) \
            .all()

        if not results:
            return jsonify({
                'provinces': [],
                'counts': []
            }), 404

        # 分离省份和数量数据
        provinces = [result[0] for result in results]
        counts = [result[1] for result in results]

        return jsonify({
            'provinces': provinces,
            'counts': counts
        })
    except Exception as e:
        logger.error(f"获取高等级景区数据失败: {str(e)}", exc_info=True)
        return jsonify({
            'error': str(e)
        }), 500